﻿using System;
using System.Collections;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using PulsatrixService.Models;

namespace PulsatrixService
{
    public class DatabaseService
    {
        private string ConnectionString { get; set; }

        #region Singleton
        private static DatabaseService instance;
        private DatabaseService()
        {
            string connection = ConfigurationManager.ConnectionStrings["PulsatrixDbEntities"].ConnectionString;
            this.ConnectionString = connection.Substring(connection.IndexOf("data source"), connection.IndexOf("App=EntityFramework") - connection.IndexOf("data source"));
        }
        public static DatabaseService Instance
        {
            get
            {
                if (instance == null)
                    instance = new DatabaseService();
                return instance;
            }
        }
        #endregion

        #region Static data
        private UniversityBrief[] _univBrief;
        private UniversityBrief[] UniversitiesBrief
        {
            get
            {
                if (_univBrief == null)
                {
                    SqlCommand myCommand = new SqlCommand(StringData.GetAllUniversitiesBrief);
                    SqlConnection con = new SqlConnection(this.ConnectionString);
                    con.Open();
                    myCommand.Connection = con;
                    SqlDataReader reader = myCommand.ExecuteReader();

                    List<UniversityBrief> univs = new List<UniversityBrief>();
                    UniversityBrief univ;
                    while (reader.Read())
                    {
                        univ = new UniversityBrief();

                        univ.ID = reader.GetInt32(0);
                        univ.Name = reader.GetString(1);
                        univ.ImagePath = reader.IsDBNull(2) ? null : reader.GetString(2);
                        univ.Category = "Університет";
                        univ.YearId = reader.GetInt32(3);

                        univs.Add(univ);
                    }
                    con.Close();

                    _univBrief = univs.ToArray();
                }
                return _univBrief;
            }
            set
            {
                if (_univBrief == null)
                    _univBrief = value;
            }
        }

        private static University[] _universities;
        private University[] Universities
        {
            get
            {
                if (_universities == null)
                {
                    SqlCommand myCommand = new SqlCommand(StringData.GetAllUniversities);
                    SqlConnection con = new SqlConnection(this.ConnectionString);
                    con.Open();
                    myCommand.Connection = con;
                    SqlDataReader reader = myCommand.ExecuteReader();

                    UniversityType[] types = GetUniversityTypes();
                    City[] cities = GetCities();
                    Region[] regions = GetRegions();
                    State[] states = GetStates();
                    Year[] years = GetYears();

                    List<University> univs = new List<University>();
                    University univ;
                    int type, city, region, state, year;
                    while (reader.Read())
                    {
                        univ = new University();

                        univ.Category = "Університет";
                        univ.ID = reader.GetInt32(0);
                        univ.Name = reader.GetString(1);
                        univ.Abbreviation = reader.IsDBNull(2)? null : reader.GetString(2);

                        type = reader.GetInt32(3);
                        for (int i = 0; i < types.Length; i++)
                        {
                            if (types[i].ID == type)
                            {
                                univ.Type = types[i].Value;
                                break;
                            }
                        }

                        city = reader.GetInt32(4);
                        for (int i = 0; i < cities.Length; i++)
                        {
                            if (cities[i].ID == city)
                            {
                                univ.City = cities[i].Value;
                                break;
                            }
                        }

                        region = reader.GetInt32(5);
                        for (int i = 0; i < regions.Length; i++)
                        {
                            if (regions[i].ID == region)
                            {
                                univ.Region = regions[i].Value;
                                break;
                            }
                        }

                        state = reader.GetInt32(6);
                        for (int i = 0; i < states.Length; i++)
                        {
                            if (states[i].ID == state)
                            {
                                univ.State = states[i].Value;
                                break;
                            }
                        }
                        univ.Address = reader.IsDBNull(7)? null : reader.GetString(7);
                        univ.Location = reader.IsDBNull(8) ? null : reader.GetString(8);
                        univ.ShortInfo = reader.IsDBNull(9) ? null : reader.GetString(9);
                        univ.Accreditation = reader.IsDBNull(10) ? null : reader.GetString(10);
                        univ.LogoPath = reader.IsDBNull(11) ? null : reader.GetString(11);
                        univ.PicsPath = reader.IsDBNull(12) ? null : reader.GetString(12);
                        univ.Phone = reader.IsDBNull(13) ? null : reader.GetString(13);
                        univ.Email = reader.IsDBNull(14)? null : reader.GetString(14);
                        univ.StudentsAmount = reader.IsDBNull(15) ? 0 : reader.GetInt32(15);
                        univ.TeachersAmount = reader.IsDBNull(16) ? 0 : reader.GetInt32(16);
                        univ.CandidatesScAmount = reader.IsDBNull(17) ? 0 : reader.GetInt32(17);
                        univ.PhDAmount = reader.IsDBNull(18) ? 0 : reader.GetInt32(18);
                        univ.ApplyingInfo = reader.IsDBNull(19) ? null : reader.GetString(19);
                        univ.UnivDetailedInfo = reader.IsDBNull(20) ? null : reader.GetString(20);
                        univ.SourceLink = reader.IsDBNull(21) ? null : reader.GetString(21);
                        univ.CostOfTraining = reader.IsDBNull(22) ? null : reader.GetString(22);
                        univ.FormOfStudying = reader.IsDBNull(23) ? null : reader.GetString(23);

                        if(!reader.IsDBNull(24)) univ.FreeStudying = reader.GetBoolean(24);
                        if (!reader.IsDBNull(25)) univ.Hostel = reader.GetBoolean(25);
                        if (!reader.IsDBNull(26)) univ.IsPrivate = reader.GetBoolean(26);
                        if (!reader.IsDBNull(27)) univ.MilitaryDepartment = reader.GetBoolean(27);
                        if (!reader.IsDBNull(28)) univ.PhD = reader.GetBoolean(28);

                        univ.Ratings = reader.IsDBNull(29) ? null : reader.GetString(29);
                        univ.Website = reader.IsDBNull(30) ? null : reader.GetString(30);

                        if (!reader.IsDBNull(31)) univ.PostGraduateEducation = reader.GetBoolean(31);
                        if (!reader.IsDBNull(32)) univ.TrainingDepartment = reader.GetBoolean(32);
                        if (!reader.IsDBNull(33)) univ.PreparationCourses = reader.GetBoolean(33);

                        univ.PostalIndex = reader.IsDBNull(34) ? null : reader.GetString(34);

                        year = reader.IsDBNull(35) ? 0 : reader.GetInt32(35);
                        for (int i = 0; i < years.Length; i++)
                        {
                            if (years[i].ID == year)
                            {
                                univ.Year = years[i].Value;
                                break;
                            }
                        }

                        univs.Add(univ);
                    }
                    _universities = univs.ToArray();
                }

                return _universities;
            }
            set
            {
                if (_universities == null)
                    _universities = value;
            }
        }
        #endregion

        /// <summary>
        /// Loads the university types. Each time it is loaded from the db.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public UniversityType[] GetUniversityTypes()
        {
            SqlCommand myCommand = new SqlCommand(StringData.GetUniversityTypes);
            SqlConnection con = new SqlConnection(this.ConnectionString);
            con.Open();
            myCommand.Connection = con;
            SqlDataReader reader = myCommand.ExecuteReader();

            List<UniversityType> types = new List<UniversityType>();
            UniversityType type;
            while (reader.Read())
            {
                type = new UniversityType();
                type.ID = reader.GetInt32(0);
                type.Value = reader.GetString(1);
                types.Add(type);
            }
            return types.ToArray();
        }

        /// <summary>
        /// Loads the states. Each time it is loaded from the db.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public State[] GetStates()
        {
            SqlCommand myCommand = new SqlCommand(StringData.GetStates);
            SqlConnection con = new SqlConnection(this.ConnectionString);
            con.Open();
            myCommand.Connection = con;
            SqlDataReader reader = myCommand.ExecuteReader();

            List<State> states = new List<State>();
            State state;
            while (reader.Read())
            {
                state = new State();
                state.ID = reader.GetInt32(0);
                state.Value = reader.GetString(1);
                states.Add(state);
            }
            return states.ToArray();
        }

        /// <summary>
        /// Loads the regions. Each time it is loaded from the db.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public Region[] GetRegions()
        {
            SqlCommand myCommand = new SqlCommand(StringData.GetRegions);
            SqlConnection con = new SqlConnection(this.ConnectionString);
            con.Open();
            myCommand.Connection = con;
            SqlDataReader reader = myCommand.ExecuteReader();

            List<Region> regions = new List<Region>();
            Region region;
            while (reader.Read())
            {
                region = new Region();
                region.ID = reader.GetInt32(0);
                region.Value = reader.GetString(1);
                regions.Add(region);
            }
            return regions.ToArray();
        }

        /// <summary>
        /// Loads the cities. Each time it is loaded from the db.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public City[] GetCities()
        {
            SqlCommand myCommand = new SqlCommand(StringData.GetCities);
            SqlConnection con = new SqlConnection(this.ConnectionString);
            con.Open();
            myCommand.Connection = con;
            SqlDataReader reader = myCommand.ExecuteReader();

            List<City> cities = new List<City>();
            City city;

            while (reader.Read())
            {
                city = new City();
                city.ID = reader.GetInt32(0);
                city.Value = reader.GetString(1);
                cities.Add(city);
            }
            con.Close();
            return cities.ToArray();
        }

        /// <summary>
        /// Loads the years. Each time it is loaded from the db.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public Year[] GetYears()
        {
            SqlCommand myCommand = new SqlCommand(StringData.GetYears);
            SqlConnection con = new SqlConnection(this.ConnectionString);
            con.Open();
            myCommand.Connection = con;
            SqlDataReader reader = myCommand.ExecuteReader();

            List<Year> years = new List<Year>();
            Year year;
            while (reader.Read())
            {
                year = new Year();
                year.ID = reader.GetInt32(0);
                year.Value = reader.GetInt32(1);
                years.Add(year);
            }
            return years.ToArray();
        }

        /// <summary>
        /// Loads the universities. From singleton.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public University[] GetUniversities()
        {
            return Universities;
        }

        /// <summary>
        /// Loads the short info about universities. From singleton.
        /// </summary>
        /// <returns>Model with ID in it.</returns>
        public UniversityBrief[] GetUniversitiesBrief()
        {
            return UniversitiesBrief;
        }

        /// <summary>
        /// Main search method. Now only for Universities.
        /// </summary>
        /// <param name="query">Search query</param>
        /// <returns>Bunch of answers, sorted by % of phrases.</returns>
        public SearchModel[] Search(string query)
        {
            int yearId = GetSufficientYear();

            if (query == null)
                throw new ArgumentException("Input parameter cannot be null.");

            query = RemoveInsideSpaces(query.ToLower().Split(' '));
            String[] parts = query.Split(' ');

            List<SearchModel> result = new List<SearchModel>();

            if (query.Length <= 3)
            {
                for (int i = 0; i < UniversitiesBrief.Length; i++)
                {
                    if (UniversitiesBrief[i].YearId == yearId && UniversitiesBrief[i].Name.ToLower().Substring(0, query.Length) == query)
                    {
                        result.Add(new SearchModel()
                        {
                            Name = UniversitiesBrief[i].Name,
                            Category = UniversitiesBrief[i].Category
                        });
                    }
                }
            }
            else
            {
                List<SearchModel> smallPercent = new List<SearchModel>();
                for (int i = 0; i < UniversitiesBrief.Length; i++)
                {
                    // looking for 100% containers
                    if (UniversitiesBrief[i].YearId == yearId && UniversitiesBrief[i].Name.ToLower().Contains(query))
                    {
                        result.Add(new SearchModel()
                        {
                            Name = UniversitiesBrief[i].Name,
                            Category = UniversitiesBrief[i].Category
                        });
                    }
                    else // looking for items with words, which could be containers for query words
                    {
                        for (int j = 0; j < parts.Length; j++)
                        {
                            if (UniversitiesBrief[i].YearId == yearId && UniversitiesBrief[i].Name.ToLower().Contains(parts[j]))
                            {
                                smallPercent.Add(new SearchModel()
                                {
                                    Name = UniversitiesBrief[i].Name,
                                    Category = UniversitiesBrief[i].Category
                                });
                                break;
                            }
                        }
                    }
                }

                foreach (var item in smallPercent)
                {
                    result.Add(item);
                }
            }
            return result.ToArray();
        }

        private int GetSufficientYear() // CHANGE THIS VALUE WHEN DB WILL BE REFRESHED
        {
            return 14;
        }
        private string RemoveSpaces(string input)
        {
            if (input != string.Empty && input != null)
            {
                int start = -1;
                char sign = ' ';
                while (sign == ' ')
                {
                    start++;
                    sign = input[start];
                }
                int end = input.Length;
                sign = ' ';
                while (sign == ' ')
                {
                    end--;
                    sign = input[end];
                }
                return input.Substring(start, end + 1 - start);
            }
            else return string.Empty;
        }
        private string RemoveInsideSpaces(string[] parts)
        {
            string result = "";
            for (int i = 0; i < parts.Length; i++)
            {
                if (parts[i] != "")
                    result += RemoveSpaces(parts[i]) + " ";
            }
            return result.Substring(0, result.Length - 1);
        }
    }
}
